package com.shelpe.services.sinbad.controller.sinbad;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Order;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.shelpe.services.sinbad.controller.dto.EffectDto;
import com.shelpe.services.sinbad.entity.jpa.User;
import com.shelpe.services.sinbad.entity.jpa.UserInfo;
import com.shelpe.services.sinbad.repository.jpa.UserInfoRepository;
import com.shelpe.services.sinbad.repository.jpa.UserRepository;
import com.shelpe.services.sinbad.service.rpt.UserService;
import com.shelpe.services.sinbad.utils.DateUtil;

import io.swagger.annotations.Api;
import lombok.Data;

@RestController
@RequestMapping(value = "/sinbadservice/v2")
@Api("生成excel相关的API")
public class ExcelController {
	@Autowired
	private UserRepository userRepository;
	@Autowired
	private UserInfoRepository userInfoRepository;
	@Autowired
	private UserService userService;

	@SuppressWarnings({ "deprecation", "resource" })
	@RequestMapping(method = RequestMethod.GET, path = "/excel/xxxxx")
	public void rptExcel() {
		// 第一步，创建一个webbook，对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet("账户信息表 ");
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
		HSSFRow row = sheet.createRow((int) 0);
		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

		HSSFCell cell = row.createCell((short) 0);
		cell.setCellValue("用户名");
		cell.setCellStyle(style);
		cell = row.createCell((short) 1);
		cell.setCellValue("花费");
		cell.setCellStyle(style);
		cell = row.createCell((short) 2);
		cell.setCellValue("账户roi");
		cell.setCellStyle(style);
		cell = row.createCell((short) 3);
		cell.setCellValue("账户ppc");
		cell.setCellStyle(style);// 点击量、点击率、成交金额待加
		cell = row.createCell((short) 4);
		cell.setCellValue("账户点击量");
		cell.setCellStyle(style);
		cell = row.createCell((short) 5);
		cell.setCellValue("账户点击率(单位:%)");
		cell.setCellStyle(style);
		cell = row.createCell((short) 6);
		cell.setCellValue("账户成交金额");
		cell.setCellStyle(style);

		// 第五步，写入实体数据 实际应用中这些数据从数据库得到，
		List<User> users = this.userRepository.findAll(new Specification<User>() {
			@Override
			public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
				Predicate p = cb.equal(root.get("userType"), 1);
				p = cb.and(p, cb.equal(root.get("established"), 1));
				p = cb.and(p, cb.greaterThan(root.get("purchaseTime"), new Date()));

				query.where(p);
				List<Order> orders = new ArrayList<Order>();
				orders.add(cb.desc(root.get("createdTime")));
				query.orderBy(orders);
				return query.getRestriction();
			}
		});

		List<UserInfo> userInfos = (List<UserInfo>) this.userInfoRepository.findAll();
		Map<Long, UserInfo> map = new HashMap<Long, UserInfo>();
		for (UserInfo info : userInfos) {
			map.put(info.getUserID(), info);
		}

		List<User> needUsers = new ArrayList<User>();
		if (users != null && users.size() > 0) {
			for (User user : users) {
				if (map.get(user.getUserID()) != null) {
					needUsers.add(user);
				}
			}
		}

		Date yesterday = DateUtils.addDays(new Date(), -1);
		String startDate = DateFormatUtils.format(DateUtils.addDays(yesterday, -6), "yyyy-MM-dd");
		String endDate = DateFormatUtils.format(yesterday, "yyyy-MM-dd");

		List<userEffectDto> effects = new ArrayList<userEffectDto>();
		if (needUsers != null && needUsers.size() > 0) {
			for (int i = 0; i < needUsers.size(); i++) {
				User user = needUsers.get(i);
				System.out.println("-----------------------------------------------------------------size:" + i
						+ "nick:" + user.getNick());
				if (this.filterNotExistUser(user.getUserID())) {
					continue;
				}

				userEffectDto userDto = new userEffectDto();
				userDto.setEffect(this.userService.summariesMetric(user.getUserID(), "all", startDate, endDate));
				userDto.setNick(user.getNick());
				effects.add(userDto);

			}
		}

		if (effects == null || effects.size() == 0) {
			throw new IllegalArgumentException("effects_not_foud");
		}

		for (int i = 0; i < effects.size(); i++) {
			row = sheet.createRow((int) i + 1);
			userEffectDto stu = effects.get(i);
			// 第四步，创建单元格，并设置值
			row.createCell((short) 0).setCellValue(stu.getNick());
			row.createCell((short) 1).setCellValue(DateUtil.formatDouble2((double) stu.getEffect().getCost() / 100));
			if(stu.getEffect().getCost() == 0){
				row.createCell((short) 2).setCellValue((double) 0.0);//roi
			}else {
				row.createCell((short) 2).setCellValue((double) DateUtil.formatDouble2((float)stu.getEffect().getPayTotal()/stu.getEffect().getCost()));
			}
			if(stu.getEffect().getClick() == 0){
				row.createCell((short) 3).setCellValue((double) 0.0);//ppc
			}else {
				row.createCell((short) 3).setCellValue((double) DateUtil.formatDouble2((float)stu.getEffect().getPayTotal()/100/stu.getEffect().getClick()));
			}
			row.createCell((short) 4).setCellValue((double) DateUtil.formatDouble2(stu.getEffect().getClick()));//click
			if(stu.getEffect().getImpressions() == 0){
				row.createCell((short) 5).setCellValue((double)0.0);//ctr
			}else {
				row.createCell((short) 5).setCellValue((double) DateUtil.formatDouble2((100*(float)stu.getEffect().getClick()/stu.getEffect().getImpressions())));
			}
			row.createCell((short) 6).setCellValue((double) DateUtil.formatDouble2((float)stu.getEffect().getPayTotal()/100));//sale
		}
		// 第六步，将文件存到指定位置
		try {
			FileOutputStream fout = new FileOutputStream("D:/sinbaduserreport.xls");
			wb.write(fout);
			fout.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private boolean filterNotExistUser(long userID) {
		List<Long> userIDs = new ArrayList<Long>();
		userIDs.add(622559095L);
		userIDs.add(2813675052L);
		userIDs.add(474932459L);
		userIDs.add(36445168L);
		userIDs.add(2053917669L);
		if (userIDs.indexOf(userID) > -1) {
			return true;
		}
		return false;
	}

	@Data
	private static class userEffectDto {
		private String nick;
		private EffectDto effect;
	}
}
